package com.echo.dao;

import com.echo.entity.Comment;
import com.echo.util.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 评论数据访问层：负责comments表的CRUD操作
 */
public class CommentDao {
    private static final Logger logger = Logger.getLogger(CommentDao.class.getName());

    /**
     * 新增评论
     * @param comment 评论对象
     * @return 自动生成的评论ID
     * @throws SQLException 数据库操作异常
     */
    public int insert(Comment comment) throws SQLException {
        System.out.println("===== 开始执行数据库插入操作 =====");
        System.out.println("准备插入评论数据: article_id=" + comment.getArticleId() + ", user_id=" + comment.getUserId());
        
        String sql = "INSERT INTO comments (article_id, user_id, content, parent_id, create_time, is_deleted) "+
                "VALUES (?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = JdbcUtils.getConnection();
             // 使用RETURN_GENERATED_KEYS选项来获取自动生成的ID
             PreparedStatement pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            
            logger.info("准备插入评论: articleId=" + comment.getArticleId() + ", userId=" + comment.getUserId());
            System.out.println("设置SQL参数");
            
            // 设置参数
            pstmt.setInt(1, comment.getArticleId());
            pstmt.setInt(2, comment.getUserId());
            pstmt.setString(3, comment.getContent());
            
            // 父评论ID可能为null
            if (comment.getParentId() != null) {
                pstmt.setInt(4, comment.getParentId());
                System.out.println("parent_id设置为: " + comment.getParentId());
            } else {
                pstmt.setNull(4, java.sql.Types.INTEGER);
                System.out.println("parent_id设置为NULL");
            }
            
            pstmt.setTimestamp(5, new Timestamp(comment.getCreateTime().getTime()));
            pstmt.setInt(6, comment.getIsDeleted() != null ? comment.getIsDeleted() : 0);
            
            System.out.println("执行SQL语句: " + sql);
            int rows = pstmt.executeUpdate();
            System.out.println("SQL执行完成，影响行数: " + rows);
            
            // 获取自动生成的ID
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    int generatedId = generatedKeys.getInt(1);
                    System.out.println("成功获取自动生成的评论ID: " + generatedId);
                    logger.info("评论插入成功，ID: " + generatedId + ", 影响行数: " + rows);
                    System.out.println("数据库插入操作完成，返回评论ID: " + generatedId);
                    return generatedId;
                } else {
                    logger.severe("插入评论成功但无法获取自动生成的ID");
                    throw new SQLException("插入评论成功但无法获取自动生成的ID");
                }
            }
        } catch (SQLException e) {
            System.out.println("数据库插入操作失败: " + e.getMessage());
            logger.log(Level.SEVERE, "插入评论失败: " + e.getMessage(), e);
            throw e;
        }
    }

    /**
     * 根据文章ID分页查询评论列表（仅查询顶级评论，即parentId为null的评论）
     * @param articleId 文章ID
     * @param currentPage 当前页码
     * @param pageSize 每页显示条数
     * @return 评论列表
     * @throws SQLException 数据库操作异常
     */
    public List<Comment> findByArticleId(Integer articleId, Integer currentPage, Integer pageSize) throws SQLException {
        System.out.println("===== CommentDao.findByArticleId 开始执行 =====");
        System.out.println("查询参数: articleId=" + articleId + ", currentPage=" + currentPage + ", pageSize=" + pageSize);
        
        // 特别针对articleId=8添加额外调试信息
        if (articleId != null && articleId == 8) {
            System.out.println("[DEBUG-ARTICLE-8] 正在查询文章ID为8的评论列表");
        }
        
        List<Comment> comments = new ArrayList<>();
        
        // 计算偏移量
        int offset = (currentPage - 1) * pageSize;
        System.out.println("CommentDao.findByArticleId: 计算offset: currentPage=" + currentPage + ", pageSize=" + pageSize + ", offset=" + offset);
        
        // 查询顶级评论（parentId为null或0），按创建时间倒序，连接users表获取用户名
        String sql = "SELECT c.id, c.article_id, c.user_id, u.username, c.content, c.parent_id, c.create_time, c.is_deleted " +
                "FROM comments c " +
                "LEFT JOIN users u ON c.user_id = u.id " +
                "WHERE c.article_id = ? AND c.is_deleted = 0 AND (c.parent_id IS NULL OR c.parent_id = 0) " +
                "ORDER BY c.create_time DESC " +
                "LIMIT ? OFFSET ?";
        
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            logger.info("查询文章评论列表: articleId=" + articleId + ", page=" + currentPage + ", pageSize=" + pageSize);
            System.out.println("CommentDao.findByArticleId: 执行SQL: " + sql);
            System.out.println("CommentDao.findByArticleId: 参数 articleId=" + articleId + ", pageSize=" + pageSize + ", offset=" + offset);
            
            // 特别针对articleId=8添加调试查询
            if (articleId != null && articleId == 8) {
                System.out.println("[DEBUG-ARTICLE-8] 执行额外调试查询，检查文章8的所有评论(包括已删除和回复)");
                String debugSql = "SELECT id, article_id, user_id, content, parent_id, is_deleted FROM comments WHERE article_id = 8";
                PreparedStatement debugPstmt = conn.prepareStatement(debugSql);
                ResultSet debugRs = debugPstmt.executeQuery();
                int debugCount = 0;
                while (debugRs.next()) {
                    debugCount++;
                    System.out.println("[DEBUG-ARTICLE-8] 评论" + debugCount + ": id=" + debugRs.getInt("id") + ", user_id=" + 
                                     debugRs.getInt("user_id") + ", parent_id=" + debugRs.getObject("parent_id") + 
                                     ", is_deleted=" + debugRs.getInt("is_deleted"));
                }
                debugRs.close();
                debugPstmt.close();
            }
            
            pstmt.setInt(1, articleId);
            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, offset);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("CommentDao.findByArticleId: 查询执行成功，开始处理结果集");
                int rowCount = 0;
                while (rs.next()) {
                    Comment comment = mapResultSetToComment(rs);
                    comments.add(comment);
                    rowCount++;
                    System.out.println("CommentDao.findByArticleId: 处理第" + rowCount + "条记录: id=" + comment.getId() + ", userId=" + comment.getUserId() + ", content=" + comment.getContent().substring(0, Math.min(20, comment.getContent().length())) + "...");
                }
                System.out.println("CommentDao.findByArticleId: 结果集处理完成，共" + rowCount + "条记录");
                
                // 针对空结果添加额外调试信息
                if (rowCount == 0) {
                    System.out.println("[DEBUG] 未找到评论记录，检查条件: article_id=" + articleId + ", is_deleted=0, parent_id IS NULL");
                }
            }
            
            System.out.println("CommentDao.findByArticleId: 返回评论列表大小: " + comments.size());
            logger.info("查询完成，获取评论数: " + comments.size());
            System.out.println("===== CommentDao.findByArticleId 执行完成 =====");
            return comments;
        } catch (SQLException e) {
            System.out.println("CommentDao.findByArticleId: 异常: " + e.getMessage());
            logger.log(Level.SEVERE, "查询评论列表失败: " + e.getMessage(), e);
            throw e;
        }
    }

    /**
     * 查询指定评论的回复列表
     * @param parentId 父评论ID
     * @return 回复列表
     * @throws SQLException 数据库操作异常
     */
    public List<Comment> findRepliesByParentId(Integer parentId) throws SQLException {
        List<Comment> replies = new ArrayList<>();
        
        String sql = "SELECT c.id, c.article_id, c.user_id, u.username, c.content, c.parent_id, c.create_time, c.is_deleted " +
                "FROM comments c " +
                "LEFT JOIN users u ON c.user_id = u.id " +
                "WHERE c.parent_id = ? AND c.is_deleted = 0 " +
                "ORDER BY c.create_time ASC";
        
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            logger.info("查询评论回复: parentId=" + parentId);
            
            pstmt.setInt(1, parentId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Comment comment = mapResultSetToComment(rs);
                    replies.add(comment);
                }
            }
            
            logger.info("查询完成，获取回复数: " + replies.size());
            return replies;
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "查询评论回复失败: " + e.getMessage(), e);
            throw e;
        }
    }

    /**
     * 统计指定文章的顶级评论总数（不包括回复）
     * @param articleId 文章ID
     * @return 评论总数
     * @throws SQLException 数据库操作异常
     */
    public int countByArticleId(Integer articleId) throws SQLException {
        // 查询顶级评论总数（parentId为null或0）
        String sql = "SELECT COUNT(*) FROM comments WHERE article_id = ? AND is_deleted = 0 AND (parent_id IS NULL OR parent_id = 0)";
        
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            logger.info("统计文章评论数: articleId=" + articleId);
            System.out.println("CommentDao.countByArticleId: 执行SQL: " + sql);
            System.out.println("CommentDao.countByArticleId: 参数 articleId=" + articleId);
            
            pstmt.setInt(1, articleId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("CommentDao.countByArticleId: 查询执行成功");
                if (rs.next()) {
                    int count = rs.getInt(1);
                    System.out.println("CommentDao.countByArticleId: 查询结果 count=" + count);
                    logger.info("文章评论总数: " + count);
                    return count;
                }
                System.out.println("CommentDao.countByArticleId: 查询结果为空");
            }
            return 0;
        } catch (SQLException e) {
            System.out.println("CommentDao.countByArticleId: 异常: " + e.getMessage());
            logger.log(Level.SEVERE, "统计评论数失败: " + e.getMessage(), e);
            throw e;
        }
    }

    /**
     * 根据ID查找评论
     * @param id 评论ID
     * @return 评论对象，如果不存在返回null
     * @throws SQLException 数据库操作异常
     */
    public Comment findById(Integer id) throws SQLException {
        if (id == null || id <= 0) {
            return null;
        }
        
        String sql = "SELECT id, article_id, user_id, content, parent_id, create_time, is_deleted " +
                     "FROM comments WHERE id = ? AND is_deleted = 0";
        
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Comment comment = new Comment();
                    comment.setId(rs.getInt("id"));
                    comment.setArticleId(rs.getInt("article_id"));
                    comment.setUserId(rs.getInt("user_id"));
                    comment.setContent(rs.getString("content"));
                    
                    // 处理可能为null的parentId
                    int parentId = rs.getInt("parent_id");
                    if (!rs.wasNull()) {
                        comment.setParentId(parentId);
                    }
                    
                    comment.setCreateTime(rs.getTimestamp("create_time"));
                    comment.setIsDeleted(rs.getInt("is_deleted"));
                    
                    return comment;
                }
                return null;
            }
        }
    }

    /**
     * 物理删除评论
     * @param id 评论ID
     * @return 影响行数
     * @throws SQLException 数据库操作异常
     */
    public int delete(Integer id) throws SQLException {
        String sql = "DELETE FROM comments WHERE id = ?";
        
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            logger.info("删除评论: id=" + id);
            
            pstmt.setInt(1, id);
            
            int rows = pstmt.executeUpdate();
            logger.info("评论删除完成，影响行数: " + rows);
            return rows;
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "删除评论失败: " + e.getMessage(), e);
            throw e;
        }
    }

    /**
     * 将ResultSet映射为Comment对象
     * @param rs ResultSet对象
     * @return Comment对象
     * @throws SQLException 数据库操作异常
     */
    private Comment mapResultSetToComment(ResultSet rs) throws SQLException {
        Comment comment = new Comment();
        comment.setId(rs.getInt("id"));
        comment.setArticleId(rs.getInt("article_id"));
        comment.setUserId(rs.getInt("user_id"));
        comment.setUsername(rs.getString("username"));
        comment.setContent(rs.getString("content"));
        
        // 处理可能为null的parentId
        int parentId = rs.getInt("parent_id");
        if (!rs.wasNull()) {
            comment.setParentId(parentId);
        }
        
        comment.setCreateTime(rs.getTimestamp("create_time"));
        comment.setIsDeleted(rs.getInt("is_deleted"));
        
        return comment;
    }
}